using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;
using Christian.Helle.PocketNews.PocketPC.NewsProtocol;

namespace Christian.Helle.PocketNews.PocketPC.Data
{
    public class SqlCeDataProvider : IDataProvider
    {
        #region IDataProvider Members

        public string[] GetCachedNewsgroups()
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.TableDirect;
                cmd.CommandText = "Newsgroup";
                cmd.IndexName = "IDX_NewsgroupName";

                using (var reader = cmd.ExecuteReader())
                {
                    var list = new List<string>();

                    while (reader.Read())
                    {
                        list.Add(reader.GetString(1));
                    }

                    return list.ToArray();
                }
            }
        }

        public List<ArticleHeader> GetArticlesEx(string newsgroup, int maxcount)
        {
            List<ArticleHeader> list;
            if (maxcount != 0)
            {
                list = new List<ArticleHeader>(maxcount);
            }
            else
            {
                list = new List<ArticleHeader>();
            }

            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                if (DoesTableExist(cmd, newsgroup))
                {
                    cmd.CommandType = CommandType.TableDirect;
                    cmd.CommandText = newsgroup;
                    cmd.IndexName = string.Format("IDX_{0}_DESC", newsgroup);

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (maxcount != 0)
                        {
                            for (var i = 0; i < maxcount; i++)
                            {
                                if (reader.Read())
                                {
                                    AddArticleHeader(ref list, reader);
                                }
                            }
                        }
                        else
                        {
                            while (reader.Read())
                            {
                                AddArticleHeader(ref list, reader);
                            }
                        }
                    }
                }
            }

            return list;
        }

        public void UpdateNewsgroupCache(string[] groups)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "DELETE FROM Newsgroup";
                cmd.ExecuteNonQuery();

                cmd.CommandType = CommandType.TableDirect;
                cmd.CommandText = "Newsgroup";

                using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
                {
                    var rec = rs.CreateRecord();
                    for (var i = 0; i < groups.Length; i++)
                    {
                        rec.SetString(1, groups[i]);
                        rec.SetInt32(2, 0);
                        rec.SetInt32(3, 0);
                        rec.SetInt32(4, 0);
                        rs.Insert(rec);
                    }
                }
            }
        }

        public string[] SearchNewsgroups(string parameter)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format(
                    @"SELECT GroupName 
						FROM Newsgroup 
						WHERE GroupName LIKE '%' + '{0}' + '%'",
                    parameter);

                return PopulateStringList(cmd);
            }
        }

        public void SaveArticlesEx(ArticleHeader[] headers, string newsgroup)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                if (!DoesTableExist(cmd, newsgroup))
                {
                    CreateNewsgroupTable(newsgroup);
                }

                cmd.CommandType = CommandType.TableDirect;
                cmd.CommandText = newsgroup;

                using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
                {
                    var rec = rs.CreateRecord();
                    for (var i = 0; i < headers.Length; i++)
                    {
                        try
                        {
                            rec.SetInt32(1, int.Parse(headers[i].ID));
                            rec.SetString(2, headers[i].From);
                            rec.SetString(3, headers[i].Date);
                            rec.SetString(4, headers[i].Subject);
                            rs.Insert(rec);
                        }
                        catch (SqlCeException e)
                        {
                            Debug.WriteLine(
                                string.Format("Unable to Insert {0} due to: ",
                                              headers[i]),
                                e.Message);
                        }
                    }
                }
            }
        }

        public void SubscribeToGroup(string group)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandText =
                    @"UPDATE Newsgroup
						SET Subscribed=1
						WHERE GroupName=@Group";

                cmd.Parameters.AddWithValue("@Group", group);

                var result = cmd.ExecuteNonQuery();
            }
        }

        public bool DoesTableExist(string newsgroup)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                return DoesTableExist(cmd, newsgroup);
            }
        }

        public void CreateNewsgroupTable(string newsgroup)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                CreateNewsgroupTable(cmd, newsgroup);
            }
        }

        public string[] LoadSubscriptions()
        {
            string[] groups;

            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText =
                    @"SELECT GroupName 
						FROM Newsgroup 
						WHERE Subscribed=1";

                groups = PopulateStringList(cmd);
            }

            return groups;
        }

        public void UpdateNewsgroupDetails(Newsgroup newsgroup)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandText =
                    @"UPDATE Newsgroup
						SET Low=@Low, High=@High
						WHERE GroupName=@Group";

                cmd.Parameters.AddWithValue("@Low", newsgroup.Low);
                cmd.Parameters.AddWithValue("@High", newsgroup.High);
                cmd.Parameters.AddWithValue("@Group", newsgroup.GroupName);

                var result = cmd.ExecuteNonQuery();
            }
        }

        public Newsgroup GetNewsgroupDetails(string group)
        {
            Newsgroup result = null;

            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandText =
                    @"SELECT Posts, Low, High FROM Newsgroup
						WHERE GroupName=@Group";

                cmd.Parameters.AddWithValue("@Group", group);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        result = new Newsgroup(
                            group,
                            reader.GetInt32(0),
                            reader.GetInt32(1),
                            reader.GetInt32(2));
                    }
                }
            }

            return result;
        }

        public string GetArticleBody(string newsgroup, int msg_id)
        {
            string body = null;

            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                if (DoesTableExist(cmd, newsgroup))
                {
                    cmd.CommandText = string.Format(
                        "SELECT Body FROM [{0}] WHERE MessageId={1}",
                        newsgroup, msg_id);

                    body = cmd.ExecuteScalar() as string;
                }
            }

            return body;
        }

        public void SaveArticleBody(string newsgroup, int msg_id, string body)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                if (DoesTableExist(cmd, newsgroup))
                {
                    cmd.CommandText = string.Format(
                        "UPDATE [{0}] SET Body=@Body WHERE MessageId=@MessageId",
                        newsgroup);

                    cmd.Parameters.Add("@Body", SqlDbType.NText).Value = body;
                    cmd.Parameters.Add("@MessageId", SqlDbType.Int).Value = msg_id;

                    var result = cmd.ExecuteNonQuery();
                }
            }
        }

        public List<Draft> LoadDrafts()
        {
            var list = new List<Draft>();

            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.TableDirect;
                cmd.CommandText = "Draft";
                cmd.IndexName = "IDX_Draft";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var draft = new Draft();
                        draft.UniqueId = reader.GetGuid(1);
                        draft.Newsgroup = reader.GetString(2);
                        draft.Subject = reader.GetString(3);
                        draft.Body = reader.GetString(4);
                        list.Add(draft);
                    }
                }
            }

            return list;
        }

        public void SaveDraft(Guid uniqueId, string newsgroup, string subject, string body)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandText =
                    @"INSERT INTO Draft (UniqueId, Newsgroup, Subject, Body)
					  VALUES (@UniqueId, @Newsgroup, @Subject, @Body)";

                cmd.Parameters.Add("@UniqueId", SqlDbType.UniqueIdentifier).Value = uniqueId;
                cmd.Parameters.Add("@Newsgroup", SqlDbType.NVarChar).Value = newsgroup;
                cmd.Parameters.Add("@Subject", SqlDbType.NText).Value = subject;
                cmd.Parameters.Add("@Body", SqlDbType.NText).Value = body;

                cmd.ExecuteNonQuery();
            }
        }

        public void UpdateDraft(Guid uniqueId, string newsgroup, string subject, string body)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandText =
                    @"UPDATE Draft 
					  SET UniqueId=@UniqueId, 
						  Newsgroup=@Newsgroup, 
						  Subject=@Subject, 
						  Body=@Body
					  WHERE UniqueId=@UniqueId";

                cmd.Parameters.Add("@UniqueId", SqlDbType.UniqueIdentifier).Value = uniqueId;
                cmd.Parameters.Add("@Newsgroup", SqlDbType.NVarChar).Value = newsgroup;
                cmd.Parameters.Add("@Subject", SqlDbType.NText).Value = subject;
                cmd.Parameters.Add("@Body", SqlDbType.NText).Value = body;

                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteDraft(Guid guid)
        {
            using (var cmd = (SqlCeCommand)Cache.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "DELETE FROM Draft WHERE UniqueId=@Guid";

                cmd.Parameters.AddWithValue("@Guid", guid);

                cmd.ExecuteNonQuery();
            }
        }

        #endregion

        private string[] PopulateStringList(SqlCeCommand cmd)
        {
            var list = new List<string>();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(reader.GetString(0));
                }
            }

            return list.ToArray();
        }

        private void AddArticleHeader(
            ref List<ArticleHeader> list,
            SqlCeDataReader reader)
        {
            var item = new ArticleHeader();
            item.ID = reader.GetInt32(1).ToString();
            item.From = reader.GetString(2);
            item.Date = reader.GetString(3);
            item.Subject = reader.GetString(4);
            list.Add(item);
        }

        private bool DoesTableExist(SqlCeCommand cmd, string newsgroup)
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText =
                string.Format(
                    @"SELECT COUNT(TABLE_NAME) 
						FROM INFORMATION_SCHEMA.TABLES 
						WHERE TABLE_NAME='{0}'",
                    newsgroup);

            var obj = cmd.ExecuteScalar();
            return Convert.ToBoolean(obj);
        }

        private void CreateNewsgroupTable(SqlCeCommand cmd, string newsgroup)
        {
            if (!DoesTableExist(cmd, newsgroup))
            {
                var queries = new string[3];
                queries[0] = string.Format(
                    @"CREATE TABLE [{0}]
						(
							Id INT IDENTITY(1,1) PRIMARY KEY,
							MessageId INT UNIQUE,
							Sender NVARCHAR(200),
							Date NVARCHAR(100),
							Subject NTEXT,
							Body NTEXT
						)",
                    newsgroup);

                queries[1] = string.Format(
                    "CREATE INDEX [IDX_{0}_ASC] on [{0}] (MessageId ASC)", newsgroup);

                queries[2] = string.Format(
                    "CREATE INDEX [IDX_{0}_DESC] on [{0}] (MessageId DESC)", newsgroup);

                for (var i = 0; i < queries.Length; i++)
                {
                    cmd.CommandText = queries[i];
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}